#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database if not exists edu_dwd;
use edu_dwd;
-- 事实表
-- customer_clu
drop table if exists wf_fact_customer_clue;
CREATE TABLE if not exists wf_fact_customer_clue
(
     id                       INT ,
    create_date_time         string COMMENT '创建时间',
    update_date_time         string COMMENT '最后更新时间',
    deleted                  string COMMENT '是否被删除（禁用）',
    customer_id              INT    COMMENT '客户id',
    customer_relationship_id INT    COMMENT '客户关系id',
    session_id               string       COMMENT '七陌会话id',
    sid                      string       COMMENT '访客id',
    status                   string       COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
    platform                 string       COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
    area                     string    COMMENT '区域',
    country                  string    COMMENT '所在国家',
    province                 string    COMMENT '省',
    city                     string    COMMENT '城市',
    creator                  INT            COMMENT '创建人',
    itcast_school_id         INT     COMMENT '校区Id',
    itcast_school            string  COMMENT '校区',
    itcast_subject_id        INT     COMMENT '学科Id',
    itcast_subject           string      COMMENT '学科',
    origin_type              string      COMMENT '数据来源渠道',
    clue_state               string      COMMENT '线索状态',
    is_repeat                INT          COMMENT '是否重复线索(手机号维度) 0:正常 1：重复'
)comment '客户线索表'
row format delimited
fields terminated by '\t'
tblproperties ('orc.compress'='snappy');

--customer_relationship
drop table if exists wf_fact_customer_relationship;
create table if not exists wf_fact_customer_relationship(
    id                          INT,
    create_date_time            string ,
    update_date_time            string  COMMENT '最后更新时间',
    deleted                     string  COMMENT '是否被删除（禁用）',
    customer_id                 INT     COMMENT '所属客户id',
    first_id                    INT        COMMENT '第一条客户关系id',
    origin_type                 string     COMMENT '数据来源',
    itcast_school_id            INT        COMMENT '校区Id',
    itcast_subject_id           INT        COMMENT '学科Id',
    creator                     INT        COMMENT '创建人',
    current_creator             INT        COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string     COMMENT '创建者姓名',
    origin_channel              string     COMMENT '来源渠道',
    first_customer_clue_id      INT        COMMENT '第一条线索id',
    last_customer_clue_id       INT        COMMENT '最后一条线索id'
)comment '客户意向表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

drop table if exists wf_dim_customer;
create table if not exists wf_dim_customer(

    id                       INT ,
    customer_relationship_id INT  COMMENT '当前意向id',
    create_date_time         string COMMENT '创建时间',
    update_date_time         string COMMENT '最后更新时间',
    deleted                  string  COMMENT '是否被删除（禁用）',
    name                     string  COMMENT '姓名',
    creator                  INT     COMMENT '创建人ID',
    origin_type              string  COMMENT '数据来源',
    origin_channel           string  COMMENT '来源渠道'
) comment '客户静态信息表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');
-- customer_appeal
drop table if exists wf_dim_customer_appeal;
create table if not exists wf_dim_customer_appeal(
    id                             INT  COMMENT '主键',
    customer_relationship_first_id INT  COMMENT '第一条客户关系id',
    employee_id                    INT  COMMENT '申诉人',
    employee_name                  string COMMENT '申诉人姓名',
    employee_department_id         INT  COMMENT '申诉人部门',
    employee_tdepart_id            INT  COMMENT '申诉人所属部门',
    appeal_status                  INT  COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    create_date_time               string  COMMENT '创建时间（申诉时间）',
    update_date_time               string  COMMENT '更新时间',
    deleted                        string  COMMENT '删除标志位'
)comment '线索申诉信息表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

--customer_relationship
drop table if exists wf_dim_customer_relationship;
create table if not exists wf_dim_customer_relationship(
    id                          INT,
    create_date_time            string ,
    update_date_time            string  COMMENT '最后更新时间',
    deleted                     string  COMMENT '是否被删除（禁用）',
    customer_id                 INT     COMMENT '所属客户id',
    first_id                    INT        COMMENT '第一条客户关系id',
    origin_type                 string     COMMENT '数据来源',
    itcast_school_id            INT        COMMENT '校区Id',
    itcast_subject_id           INT        COMMENT '学科Id',
    creator                     INT        COMMENT '创建人',
    current_creator             INT        COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                string     COMMENT '创建者姓名',
    origin_channel              string     COMMENT '来源渠道',
    first_customer_clue_id      INT        COMMENT '第一条线索id',
    last_customer_clue_id       INT        COMMENT '最后一条线索id'
)comment '客户意向表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

--employee
drop table if exists wf_dim_employee;
create table if not exists wf_dim_employee(
    id                  INT ,
    real_name           string    COMMENT '员工的真实姓名',
    department_id       int       COMMENT 'OA中的部门编号，有负值',
    department_name     string    COMMENT 'OA中的部门名',
    job_number          string    COMMENT '员工工号',
    creator             INT        COMMENT '创建人',
    create_date_time    string     COMMENT '创建时间',
    update_date_time    string     COMMENT '最后更新时间',
    deleted             string     COMMENT '是否被删除（禁用）',
    scrm_department_id  INT        COMMENT 'SCRM内部部门id',
    leave_office        string     COMMENT '离职状态',
    leave_office_time   string     COMMENT '离职时间',
    reinstated_time     string     COMMENT '复职时间',
    superior_leaders_id INT        COMMENT '上级领导ID',
    tdepart_id          INT        COMMENT '直属部门'
)comment '员工信息表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

--itcast_school
drop table if exists wf_dim_itcast_school;
create table if not exists wf_dim_itcast_school(
     id               INT ,
    `name`             string  COMMENT '校区名称'
)comment '校区信息表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

--itcast_subject
drop table if exists wf_dim_itcast_subject;
create table if not exists wf_dim_itcast_subject(
     id               INT ,
    deleted          string  COMMENT '是否被删除（禁用）',
    `name`             string  COMMENT '学科名称'
)comment '学科信息表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

--scrm_department
drop table if exists wf_dim_scrm_department;
create table if not exists wf_dim_scrm_department(

    id               INT ,
    `name`           string  COMMENT '部门名称',
    parent_id        INT     COMMENT '父部门id',
    create_date_time string  COMMENT '创建时间',
    update_date_time string  COMMENT '更新时间',
    deleted          string  COMMENT '删除标志'
)comment '员工部门表'
row format delimited
fields terminated by '\t'
stored as orc
tblproperties ('orc.compress'='snappy');

-- customer_clu
drop table if exists wf_dim_customer_clue;
CREATE TABLE wf_dim_customer_clue
(
    id                       INT ,
    create_date_time         string COMMENT '创建时间',
    update_date_time         string COMMENT '最后更新时间',
    deleted                  string COMMENT '是否被删除（禁用）',
    customer_id              INT    COMMENT '客户id',
    customer_relationship_id INT    COMMENT '客户关系id',
    session_id               string       COMMENT '七陌会话id',
    sid                      string       COMMENT '访客id',
    status                   string       COMMENT '状态（undeal待领取 deal 已领取 finish 已关闭 changePeer 已流转）',
    platform                 string       COMMENT '平台来源 （pc-网站咨询|wap-wap咨询|sdk-app咨询|weixin-微信咨询）',
    area                     string    COMMENT '区域',
    country                  string    COMMENT '所在国家',
    province                 string    COMMENT '省',
    city                     string    COMMENT '城市',
    creator                  INT            COMMENT '创建人',
    itcast_school_id         INT     COMMENT '校区Id',
    itcast_school            string  COMMENT '校区',
    itcast_subject_id        INT     COMMENT '学科Id',
    itcast_subject           string      COMMENT '学科',
    origin_type              string      COMMENT '数据来源渠道',
    clue_state               string      COMMENT '线索状态',
    is_repeat                INT          COMMENT '是否重复线索(手机号维度) 0:正常 1：重复'
)comment '客户线索表'
row format delimited
fields terminated by '\t'
tblproperties ('orc.compress'='snappy');"